package edu.upc.bolsatrabajo.model.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import javax.sql.DataSource;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;

import edu.upc.bolsatrabajo.model.domain.Empresa;
import edu.upc.bolsatrabajo.model.iface.IEmpresa;

@Repository
public class EmpresaImpl implements IEmpresa {
	
	@Autowired
	private DataSource dataSource;

	Connection connection = null;
	PreparedStatement statement = null;
	ResultSet resultSet = null;

	public List<Empresa> listarEmpresas(Empresa oEmpresa) {
		List<Empresa> Empresas = new ArrayList<Empresa>();

		String sqlListarEmpresas = "SELECT * FROM TAB_EMPRESA";

		try {
			connection = dataSource.getConnection();
			statement = connection.prepareStatement(sqlListarEmpresas);
			resultSet = statement.executeQuery(sqlListarEmpresas);

			while (resultSet.next()) {
				oEmpresa = new Empresa();
				oEmpresa.setNombreUsuario(resultSet.getString("C_CODIGO_USUARIO"));
				oEmpresa.setNombreEmpresa(resultSet.getString("C_NOMBRE_EMPRESA"));
				oEmpresa.setRuc(resultSet.getString("C_RUC"));
				oEmpresa.setPersonaContacto(resultSet.getString("C_PERSONA_CONTACTO"));
				oEmpresa.setCodigoPostal(resultSet.getString("C_CODIGO_POSTAL"));
				Empresas.add(oEmpresa);
			}

		} catch (Exception e) {
			throw new RuntimeException(
					"SQL exception occurred finding Empresa", e);
		} finally {
			if (resultSet != null) {
				try {
					// Close to prevent database cursor exhaustion
					resultSet.close();
				} catch (SQLException ex) {
				}
			}
			if (statement != null) {
				try {
					// Close to prevent database cursor exhaustion
					statement.close();
				} catch (SQLException ex) {
				}
			}
			if (connection != null) {
				try {
					// Close to prevent database connection exhaustion
					connection.close();
				} catch (SQLException ex) {
				}
			}
		}

		return Empresas;
	}

	public Empresa obtenerEmpresa(Empresa oEmpresa) {
		// TODO Auto-generated method stub
		return null;
	}

	public int insertarEmpresa(Empresa oEmpresa) {

		int respuesta = 0;
		String sqlInsertarEmpresa = "INSERT INTO TAB_EMPRESA " +
				" (C_CODIGO_USUARIO, C_NOMBRE_EMPRESA, C_RUC, C_PERSONA_CONTACTO, C_CODIGO_POSTAL) " +
				" VALUES (?,?,?,?,?)";
		String sqlUltimoId = "SELECT MAX(N_CODIGO_EMPRESA) AS LAST_ID FROM TAB_EMPRESA";

		try {
			connection = dataSource.getConnection();
			statement = connection.prepareStatement(sqlInsertarEmpresa);
			statement.setString(1, oEmpresa.getNombreUsuario());
			statement.setString(2, oEmpresa.getNombreEmpresa());
			statement.setString(3, oEmpresa.getRuc());
			statement.setString(4, oEmpresa.getPersonaContacto());
			statement.setString(5, oEmpresa.getCodigoPostal());
			statement.executeUpdate();
			
			statement = connection.prepareStatement(sqlUltimoId);
			resultSet = statement.executeQuery();
			
			while(resultSet.next()){
				respuesta = resultSet.getInt("LAST_ID");
			}

		} catch (Exception e) {
			//throw new RuntimeException("SQL exception occurred finding Empresa", e); 
			e.printStackTrace();
			
		} finally {
			if (resultSet != null) {
				try {
					// Close to prevent database cursor exhaustion
					resultSet.close();
				} catch (SQLException ex) {
				}
			}
			if (statement != null) {
				try {
					// Close to prevent database cursor exhaustion
					statement.close();
				} catch (SQLException ex) {
				}
			}
			if (connection != null) {
				try {
					// Close to prevent database connection exhaustion
					connection.close();
				} catch (SQLException ex) {
				}
			}
		}
		return respuesta;
	}

	public int actualizarEmpresa(Empresa oEmpresa) {

		int respuesta = 0;
		String sqlActualizarEmpresas = "UPDATE TAB_EMPRESA SET " +
				"(C_NOMBRE_EMPRESA, C_RUC, C_PERSONA_CONTACTO, C_CODIGO_POSTAL) " +
				" = (?,?,?,?) WHERE N_CODIGO_EMPRESA = ?";

		try {
			connection = dataSource.getConnection();
			statement = connection.prepareStatement(sqlActualizarEmpresas);
			statement.setString(1, oEmpresa.getNombreEmpresa());
			statement.setString(2, oEmpresa.getRuc());
			statement.setString(3, oEmpresa.getPersonaContacto());
			statement.setString(4, oEmpresa.getCodigoPostal());
			statement.setInt(5, oEmpresa.getCodigo());
			statement.executeQuery(sqlActualizarEmpresas);
			
			respuesta = 1;

		} catch (Exception e) {
			throw new RuntimeException("SQL exception occurred finding Empresa", e); 
			
		} finally {
			if (resultSet != null) {
				try {
					// Close to prevent database cursor exhaustion
					resultSet.close();
				} catch (SQLException ex) {
				}
			}
			if (statement != null) {
				try {
					// Close to prevent database cursor exhaustion
					statement.close();
				} catch (SQLException ex) {
				}
			}
			if (connection != null) {
				try {
					// Close to prevent database connection exhaustion
					connection.close();
				} catch (SQLException ex) {
				}
			}
		}
		return respuesta;
	}

	public int eliminarEmpresa(Empresa oEmpresa) {

		int respuesta = 0;
		String sqlEliminarEmpresa = "DELETE FROM TAB_EMPRESA WHERE " +
				" N_CODIGO_Empresa = ? ";

		try {
			connection = dataSource.getConnection();
			statement = connection.prepareStatement(sqlEliminarEmpresa);
			statement.setInt(1, oEmpresa.getCodigo());
			statement.executeQuery(sqlEliminarEmpresa);
			
			respuesta = 1;

		} catch (Exception e) {
			throw new RuntimeException("SQL exception occurred finding Empresa", e); 
			
		} finally {
			if (resultSet != null) {
				try {
					// Close to prevent database cursor exhaustion
					resultSet.close();
				} catch (SQLException ex) {
				}
			}
			if (statement != null) {
				try {
					// Close to prevent database cursor exhaustion
					statement.close();
				} catch (SQLException ex) {
				}
			}
			if (connection != null) {
				try {
					// Close to prevent database connection exhaustion
					connection.close();
				} catch (SQLException ex) {
				}
			}
		}
		return respuesta;
	}

}
